// This Class's job is to handle connections with database throughout the application
// Nour Eldein
package model;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;

public class DatabaseConnection {

    Map<String,Integer> status;
    Connection conn;
    Statement stmt;
    ResultSet result;
    ResultSet emailSet;
    ResultSet userNameSet;
    public DatabaseConnection(String user,String password) throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        conn = DriverManager.getConnection("jdbc:mysql://localhost/chating",user, password);
        status = new HashMap<>();
    }

    public int checkSignIn(String username, String password) throws SQLException {
        // method that takes 2 strings from the login form and checks with the data base 
        // returns 1 if the username/password is correct and returns 0 if the combination is wrong      
         int nj ;
        stmt = conn.createStatement();

        String query = "select * from User where userName= \"" + username + "\"and password= \"" + password + "\"";

        try {
            result = stmt.executeQuery(query);
        } catch (SQLException x) {
            System.out.println("error retrieving data");
        }
        if (!result.next()) {

            return 0;

        }else {
            
            //String changeStatus="update User set status='1' where userName = \""+username+"\"";
            //nj=stmt.executeUpdate(changeStatus);
            setStatus(username, 1);
           //status.put(username, 1);
            return 1;
        }
        
    }

    public int register(String name, String mail, String user, String pass, String con, String gen) throws SQLException {
       // Method used to add new user to the database 
        // parameters sent from the form except for the id and the status 
        //returns 0 if registration failed , returns 1 if registration succeeded 
        
        int z ; // numbers of affected rows in the database 
        
            stmt = conn.createStatement();

            String registerQuery = "insert into User (name,email,userName,password,country,gender,status) values (\"" + name + "\"" + ",\"" + mail + "\"" + ",\"" + user + "\"" + ",\"" + pass + "\"" + ",\"" + con + "\"" + ",\"" + gen + "\"" + ",\"" + 1 +"\""+")"+";";

            z = stmt.executeUpdate(registerQuery); 
              if(z!=0){             
                  setStatus(user, 1); 
                  return 1 ;
              } 
                else {return 0 ;}
    }

    //public int logOut (String username) throws SQLException
    public void logOut (String username) throws SQLException
    {
        status.remove(username);
       /* int z ;
        stmt=conn.createStatement();
        String logoutQuery = "update User set status='0' where userName = \""+username+"\"";
        z=stmt.executeUpdate(logoutQuery);
        if(z!=0){return 1 ;}
        else {return 0;}*/
        
        
    }
    
    public int addFriend(int user1_id, int user2_id) throws SQLException {
        // method that adds a friend request in the table User_Friend , the first parameter is sender the second is receiver 
        // sets the reqStatus to 0 which means request is pending 
        // returns 1 if insertion succeeds and returns 0 if insertion fails 

        int af = 0;
        stmt = conn.createStatement();
        String addfriendQuery = "insert into User_Friend (senderId,receiverId,reqStatus)values( \" " + user1_id + "\"" + ",\"" + user2_id + "\"" + ",\"" + 0 + "\"" + ")" + ";";
        af = stmt.executeUpdate(addfriendQuery);
        if (af != 0) {
            return 1;
        } else {
            return 0;
        }

    }

    public User[] getFriends(int id,int reqStatus) throws SQLException {
        // method to return the ids of the users friends , the only parameter is the id of the user 
        //friends are returned in the form of array of strings 
        // method only returns the friends which have already accepted the friend request of the user

        stmt = conn.createStatement();
        String count = "";
        if(reqStatus == 1)
            count = "select count(*) from User_Friend where reqStatus= 1 and ( senderId = "+id+" or receiverId = "+id+")";
        else if (reqStatus == 0){
            count = "select count(*) from User_Friend where reqStatus= 0 and receiverId = "+id+"";
        }else{
            return null;
        }
        
        ResultSet resultCount = stmt.executeQuery(count);
        
        if(resultCount.next() && resultCount.getInt(1) > 0){
            System.out.println(resultCount.getInt(1)+"here");
            User[] friends = new User[resultCount.getInt(1)];
            int i = 0 ;
            String getFriendsQuery = "";
            
            if(reqStatus == 1)
                getFriendsQuery = "SELECT userName,email,senderId AS friend  from User,User_Friend where reqStatus= 1 and receiverId = " + id + " and senderId = id UNION SELECT userName,email,receiverId AS friend  from User,User_Friend where reqStatus= 1 and senderId = " + id + " and receiverId = id";        
            else if(reqStatus == 0)
                getFriendsQuery = "SELECT userName,email,senderId AS friend  from User,User_Friend where reqStatus= 0 and receiverId = " + id + " and senderId = id";
     
            result = stmt.executeQuery(getFriendsQuery);

            while (result.next()) {
                friends[i++] = new User(result.getString(1), result.getString(2) , getStatus(result.getString(1)));
            }

            return friends;        
        }
        return null;
    }

    public User getUserInfo (String UserName) throws SQLException
    {
      stmt = conn.createStatement();
      String getUserInfoQuery="select userName , email,status from User where userName =\""+UserName+"\"";
        ResultSet userResultSet = stmt.executeQuery(getUserInfoQuery);
        while (userResultSet.next())
        {
           User x = new User(userResultSet.getString("userName"),userResultSet.getString("email"),getStatus(userResultSet.getString("userName")));
          return x ;
        }
       
       return null ;
    
    }
    
    
    
    
    
    
    
    public int getId(String userName) throws SQLException {
        
        // method to return the ids given the username
        stmt = conn.createStatement();        
        result = stmt.executeQuery("select id from User where userName = \""+userName+"\"");
        if(result.next())
            return result.getInt(1);
        return 0;
    }
    public int getUserName(String userName)throws SQLException
    {
        stmt=conn.createStatement();
        //System.out.println(userName);
        //System.out.println("select count(*) from User where userName='"+userName+"'");
        userNameSet=stmt.executeQuery("select count(*) from User where userName='"+userName+"'");
        
        if(userNameSet.next()!= false && userNameSet.getInt(1)>0)
        {
            //System.out.println("Found");
            
            return 1;
            
        }
        else
        {
            //System.out.println("No Email");
        return 0;
        }
    }
    public int getEmail(String email) throws SQLException
    {
        stmt=conn.createStatement();
        //System.out.println(email);
        //System.out.println("select count(*) from User where email='"+email+"'");
        emailSet=stmt.executeQuery("select count(*) from User where email='"+email+"'");
        
        if(emailSet.next()!= false && emailSet.getInt(1)>0)
        {
            //System.out.println("Found");
            
            return 1;
            
        }
        else
        {
            //System.out.println("No Email");
        return 0;
        }
    }
    //public int setStatus (int userId , int status) throws SQLException
    public void setStatus (String userName , int _status)
    {
        status.put(userName, _status);
        //method that sets the status of the user 
        // params : user id & status to be changed to 
        // 0 = offline ,1= online , 2=busy , 3=away 
        //returns 1 if update succeeds & 0 if update fails 
        /*int n ;
        stmt=conn.createStatement();
       String setstatusQuery="update User set status= \""+status+"\""+"where id= \""+userId+"\"";
        n=stmt.executeUpdate(setstatusQuery);
        if(n!=0){return 1 ;}
        else{return 0;}*/
     
           
    }
    
    //public int getStatus (int userId) throws SQLException
    public int getStatus (String userName)
    {   //method that returns the status of the use 
        //param : user id 
        // returns int describing the user availability status 
        if(status.containsKey(userName))
            return status.get(userName);
        return 0;
        /*int s = 0;
         stmt=conn.createStatement();     
         String getstatusQuery = "select status from User where id = \""+userId+"\"";
         result=stmt.executeQuery(getstatusQuery);
         while(result.next()){
         s=result.getInt(1);}
         return s ;  */
    }
     
    public int acceptFriend (int sender_id , int receiver_id)throws SQLException
    {  //method that sets the request status of a friend request to 1 when request is accepted 
        //params are : request sender id  , request receiver id 
        // returns 1 if the update succeeds and returns 0 if the update fails 
        
       int l ;
       stmt=conn.createStatement();
       String setreqstatusQuery="update User_Friend set reqStatus = 1 where senderId=\""+sender_id+"\""+"and receiverId=\""+receiver_id+"\"";
       l=stmt.executeUpdate(setreqstatusQuery);
       if (l!=0){return 1;}
       else{return 0;}
            
    }
    
    public int deleteFriend (int sender_id , int receiver_id)throws SQLException
    {  //method that sets the request status of a friend request to 1 when request is accepted 
        //params are : request sender id  , request receiver id 
        // returns 1 if the update succeeds and returns 0 if the update fails 
        
       int l ;
       stmt=conn.createStatement();
       String setreqstatusQuery="delete from User_Friend where senderId=\""+sender_id+"\""+"and receiverId=\""+receiver_id+"\"";
       l=stmt.executeUpdate(setreqstatusQuery);
       if (l!=0){return 1;}
       else{return 0;}
            
    }
    
           
    public int getReqStatus (int sender_id , int receiver_id)throws SQLException
    {
        // method that gets the friend request status whether it is 0 as the request is pending or 1 if the request is accepted 
        //params are : request sender id , request receiver id 
        // returns the request status ( 0 for pending and 1 for accepted ) 
        
        int rs = -1 ;
        stmt=conn.createStatement();
        String getreqstatus="select reqStatus from User_Friend where ( senderId=\""+sender_id+"\""+"and receiverId=\""+receiver_id+"\" ) or senderId=\""+receiver_id+"\""+"and receiverId=\""+sender_id+"\" ";
        result=stmt.executeQuery(getreqstatus);
        while(result.next())
        {rs=result.getInt(1);}
        return rs ;
    
    }
    
    public int countAllUsers(){
        try{
            stmt = conn.createStatement();        
            result = stmt.executeQuery("select count(*) from User");
            if(result.next())
                return result.getInt(1);
        }catch(SQLException ex){
            System.out.println("Can't Count Users");
            //ex.printStackTrace();
        }
        return 0;
    }

}
